--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_art_compstk AS (SELECT * FROM &erp.art_compstk WHERE 1=2);
DELETE FROM se_art_compstk;

/*ventilation des stock mini des RGP sur les complets stock*/
INSERT INTO se_art_compstk
(SELECT  comp_num,
         depot depot_cod,
         rang compstk_nolig,
         0 enreg_id,
         dat deb_dat,
         NULL fin_dat,
         STKMINDAT stkmini_qt,
         STKMAXDAT stkmaxi_qt
FROM (
      SELECT Nvl((SELECT comp_num FROM &erp.art_comp c WHERE c.combsto_id=p.numspecif ),0) comp_num,
            p.prodbase,p.numspecif,p.clergp,p.codgestion,
            d.depot,To_Date('31/12/1967','dd/mm/yyyy')+dat.DATAPPSTMM dat,stkmind.STKMINDAT,stkmaxd.STKMAXDAT,
            dat.RANGMULTI3+1 rang
      FROM uv_pproduits prgp,uv_pproduits p,UV_PPRODUITS_DEPOT d,UV_PPRODUITS_DATAPPSTMM dat,UV_PPRODUITS_STKMINDAT stkmind,UV_PPRODUITS_STKMAXDAT stkmaxd
      WHERE prgp.id=d.id
        AND p.clergp=prgp.id
        AND dat.id=d.id AND dat.RANGMULTI2=d.RANGMULTI2
        AND stkmind.id(+)=dat.id AND stkmind.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmind.RANGMULTI3(+)=dat.RANGMULTI3
        AND stkmaxd.id(+)=dat.id AND stkmaxd.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmaxd.RANGMULTI3(+)=dat.RANGMULTI3
        AND prgp.codgestion IN ('5') /*generique sans variante et avec variante*/
));

DELETE FROM se_art_compstk WHERE  stkmini_qt IS NULL AND stkmaxi_qt IS NULL;
DELETE FROM se_art_compstk WHERE  comp_num=0;
delete se_art_compstk s WHERE NOT EXISTS (SELECT 1 FROM &erp.art_compdepot d WHERE d.depot_cod=s.depot_cod AND d.comp_num=s.comp_num);

UPDATE se_art_compstk tf SET enreg_id=(select enreg_id FROM &erp.art_compstk f WHERE f.comp_num=tf.comp_num AND f.depot_cod=tf.depot_cod AND f.compstk_nolig=tf.compstk_nolig) ;
UPDATE se_art_compstk tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



CALL uvse_synchroverserp('comp_num@depot_cod@compstk_nolig','ART_COMPSTK','O') ;

commit;





DELETE FROM se_art_compstk;

/*stock mini des complet stock sur les complets stock*/
INSERT INTO se_art_compstk
(SELECT  comp_num,
         depot depot_cod,
         rang compstk_nolig,
         0 enreg_id,
         dat deb_dat,
         NULL fin_dat,
         STKMINDAT stkmini_qt,
         STKMAXDAT stkmaxi_qt
FROM (
      SELECT Nvl((SELECT comp_num FROM &erp.art_comp c WHERE c.combsto_id=p.numspecif ),0) comp_num,
            p.prodbase,p.numspecif,p.codgestion,
            d.depot,To_Date('31/12/1967','dd/mm/yyyy')+dat.DATAPPSTMM dat,stkmind.STKMINDAT,stkmaxd.STKMAXDAT,
            dat.RANGMULTI3+1 rang
      FROM uv_pproduits p,UV_PPRODUITS_DEPOT d,UV_PPRODUITS_DATAPPSTMM dat,UV_PPRODUITS_STKMINDAT stkmind,UV_PPRODUITS_STKMAXDAT stkmaxd
      WHERE p.id=d.id
        AND dat.id=d.id AND dat.RANGMULTI2=d.RANGMULTI2
        AND stkmind.id(+)=dat.id AND stkmind.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmind.RANGMULTI3(+)=dat.RANGMULTI3
        AND stkmaxd.id(+)=dat.id AND stkmaxd.RANGMULTI2(+)=dat.RANGMULTI2 AND stkmaxd.RANGMULTI3(+)=dat.RANGMULTI3
        AND p.codgestion IN ('3') /*generique sans variante et avec variante*/
        AND dat.DATAPPSTMM IS NOT null
));

DELETE FROM se_art_compstk WHERE  stkmini_qt IS NULL AND stkmaxi_qt IS NULL;
DELETE FROM se_art_compstk WHERE  comp_num=0;
delete se_art_compstk s WHERE NOT EXISTS (SELECT 1 FROM &erp.art_compdepot d WHERE d.depot_cod=s.depot_cod AND d.comp_num=s.comp_num);

UPDATE se_art_compstk tf SET enreg_id=(select enreg_id FROM &erp.art_compstk f WHERE f.comp_num=tf.comp_num AND f.depot_cod=tf.depot_cod AND f.compstk_nolig=tf.compstk_nolig) ;
UPDATE se_art_compstk tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



CALL uvse_synchroverserp('comp_num@depot_cod@compstk_nolig','ART_COMPSTK','O') ;

commit;
